﻿Imports System.Data
Imports System.Data.SqlClient

Public Class DBAccess

#Region "Khai báo các biến global"
    ''' <summary>
    ''' Chuỗi kết nối
    ''' </summary>    
    Dim _ConString As String
    ''' <summary>
    ''' Đối tượng sql connection
    ''' </summary>    
    Dim _Connection As SqlConnection

#End Region

#Region "Constructor"

    ''' <summary>
    ''' Constructor
    ''' </summary>
    ''' <param name="pConStr"></param>
    ''' <remarks></remarks>
    Public Sub New(pConStr As String)
        _ConString = pConStr
        _Connection = New SqlConnection(_ConString)

    End Sub

#End Region

#Region "Các hàm quản lý kết nối"

    ''' <summary>
    ''' Mở kết nối
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Open()
        If _Connection.State = ConnectionState.Closed Then
            Try
                _Connection.Open()
            Catch ex As Exception
                Throw New Exception("ERROR_OPEN_CONNECTION")
            End Try
        End If
    End Sub

    ''' <summary>
    ''' Đóng kết nối
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Close()
        If Me._Connection.State = ConnectionState.Open Then
            Me._Connection.Close()
        End If
    End Sub

    ''' <summary>
    ''' Hủy đối tượng connection
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Dispose()
        If IsNothing(_Connection) = False Then
            _Connection.Dispose()
        End If
    End Sub

    ''' <summary>
    ''' Get trạng thái của connection
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetConState() As ConnectionState
        Return _Connection.State
    End Function

#End Region

#Region "Các hàm get data"

    ''' <summary>
    ''' Ham get data tra ve dataset
    ''' </summary>
    ''' <param name="pSqlTxt">Câu sql select</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDBDataset(pSqlTxt As String) As DataSet

        Dim ds As New DataSet
        Dim cmd As New SqlCommand
        Dim da As New SqlDataAdapter

        Try

            cmd.Connection = _Connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = pSqlTxt
            da.SelectCommand = cmd

            Me.Open()

            da.Fill(ds)

        Catch ex As Exception

            ds = Nothing
            Throw ex

        Finally

            cmd.Dispose()
            cmd = Nothing
            Me.Close()            

        End Try

        Return ds

    End Function

#End Region

#Region "Các hàm execute sql"

    ''' <summary>
    ''' thực thi sql insert/update/delete
    ''' </summary>
    ''' <param name="pSqlTxt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecuteSqlString(pSqlTxt As String) As Integer

        Dim cmd As New SqlCommand
        Dim nCount As Integer

        Try
            Me.Open()

            cmd.Connection = Me._Connection
            cmd.CommandType = CommandType.Text
            cmd.CommandText = pSqlTxt

            nCount = cmd.ExecuteNonQuery()

        Catch ex As Exception

            nCount = -1
            Throw ex

        Finally

            Me.Close()

        End Try

        Return nCount
    End Function

#End Region

End Class
